package BookCreated;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.http.HttpSession;

import BookDB.h2DB;

public class BookCalnumsBean {

	private List<BookCalnums> bookcalnumslist = new ArrayList<BookCalnums>();

	public BookCalnumsBean() throws SQLException {
		Connection con = h2DB.getConnection();
		try {
			PreparedStatement ps = con
					.prepareStatement("Select calID , caLDESCRIPTION from TBLCALNUMS");
			ResultSet rs = ps.executeQuery();
			bookcalnumslist.clear();
			while (rs.next()) {
				BookCalnums bc = new BookCalnums();
				bc.setCalid(rs.getString("calID"));
				bc.setCaldescription(rs.getString("caLDESCRIPTION"));
				bookcalnumslist.add(bc);
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
	}

	public String GenCalnumsSelect(String Calid) {
		String st = new String();
		BookCalnums bc = new BookCalnums();
		for (int i = 0; i < bookcalnumslist.size(); i++) {
			bc = bookcalnumslist.get(i);
			if (Calid == bc.getCalid()) {
				st = st + "\n" + "<OPTION value=\"" + bc.getCalid()
						+ "\" Selected>" + bc.getCaldescription();
			} else {
				st = st + "\n" + "<OPTION value=\"" + bc.getCalid() + "\">"
						+ bc.getCaldescription();
			}
		}
		return st;
		// SDSD
	}

	public String GetNewBooks() throws SQLException {
		String resultSt = new String();
		Connection con = h2DB.getConnection();
		try {
			String st = "select top 5 tblbooks.calid ,tblcalnums.caldescription ,tblbooks.BookID, tblbooks.isbn, "
					+ "tblbooks.bookname , tblbooks.status, tblbooks.CREATEDATE, tblbooks.location from tblbooks"
					+ " Left join tblcalnums on (tblbooks.calid = tblcalnums.calid)"
					+ "order by CREATEDATE desc";
			PreparedStatement ps = con.prepareStatement(st);
			ResultSet rs = ps.executeQuery();
			SimpleDateFormat sf = new SimpleDateFormat("yyyy-MM-dd");
			
			while (rs.next()) {
				
				String imagepath="images\\"+rs.getString("BookID")+".jpg";
				resultSt = resultSt + "\n <tr> <td>"
						+ rs.getString("caldescription") + "</td>"
						+ "\n      <td>" + rs.getString("bookID") + "</td>"
						+ "\n      <td>" + rs.getString("isbn") + "</td>"
						+ "\n      <td>" + rs.getString("bookname") + "</td>"
						+ "\n      <td>" + sf.format(rs.getDate("CREATEDATE"))+"</td>"
						+ "\n      <td> <IMG border=0 align=left img src= "+imagepath+" width=120 height=160></TD>"
						+ "</tr>";
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		
		// System.out.println("222");
		return resultSt;
	}

	public String GetCalnumsEdit() throws SQLException {
		String resultSt = new String();
		Connection con = h2DB.getConnection();
		try {
			String st = "Select CALID, calDESCRIPTION from TBLCALNUMS "
					+ "order by calDESCRIPTION";
			PreparedStatement ps = con.prepareStatement(st);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				resultSt = resultSt + "\n" + "<tr>"
						+ "		<th><div align=\"left\">" + rs.getString("calDESCRIPTION")
						+ "</div></th>"
						+ "		<th align=\"center\" width=\"64\">"
						+ "			<form action=\"CreateCalnums\" method=\"Post\">"
						+ "   			<input type=\"hidden\" name=\"calid\" value="+ rs.getString("CALID") + ">"
						+ "				<input value=\"Edit\" type=\"submit\"> "
						+ "			</form>" + "		</th>"
						+ "		<th align=\"center\" width=\"64\">"
						+ "			<form action=\"DeleteCalnums\" method=\"Post\">"
						+ "   			<input type=\"hidden\" name=\"calid\" value="+ rs.getString("CALID") + ">"
						+ "				<input value=\"Delete\" type=\"submit\"> "
						+ "			</form>" + "		</th>" + "</tr>";
			}
			ps.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			con.close();
		}
		return resultSt;

	}
}
